/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class KhachHang_Model {

    public Vector LayDSKH() {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean TaoKhachHang(String tenkh, String lkh, String diachi, String sodt, String birthday) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " insert into khachhang(HoTen , DiaChi, SoDT, NgaySN, MaLKH, DiemTL, TinhTrang) values "
                    + " ('" + tenkh + "', '" + diachi + "', '" + sodt + "', '" + birthday + "', " + lkh + ", 0, 1) ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTenLoaiKH(int malkh, int langid) {
        String maht = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenLKH from loaikhachhang where MaNN = " + langid + " and MaLKH = " + malkh;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayTenKH(int makh) {
        String maht = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select HoTen from khachhang where  MaKH = " + makh;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayKH(int makh) {
        String maht = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select HoTen from khachhang where MaKH = " + makh + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public Vector SortHoTenKH(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang Order by HoTen " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDiemKH(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang Order by DiemTL " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortLoaiKH(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang Order by MaLKH " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTKH(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchCustomer(String hoten, String sodiem, String loaikh, String tinhtrang, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKH), HoTen, DiaChi, SoDT, NgaySN, A.MaLKH, DiemTL, A.TinhTrang "
                    + " from khachhang A, loaikhachhang B "
                    + " where A.MaLKH = B.MaLKH  ";
            String[] arr = {hoten, sodiem, loaikh, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(hoten)) {
                    query = query + " and HoTen like '%" + hoten + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(loaikh)) {
                    query = query + " and A.MaLKH = " + loaikh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + " ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchHoTenKH(String hoten, String sodiem, String loaikh, String tinhtrang, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKH), HoTen, DiaChi, SoDT, NgaySN, A.MaLKH, DiemTL, A.TinhTrang "
                    + " from khachhang A, loaikhachhang B "
                    + " where A.MaLKH = B.MaLKH  ";
            String[] arr = {hoten, sodiem, loaikh, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(hoten)) {
                    query = query + " and HoTen like '%" + hoten + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(loaikh)) {
                    query = query + " and A.MaLKH = " + loaikh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + " ";
                }
            }
            query = query + " Order by HoTen " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchDiemKH(String hoten, String sodiem, String loaikh, String tinhtrang, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKH), HoTen, DiaChi, SoDT, NgaySN, A.MaLKH, DiemTL, A.TinhTrang "
                    + " from khachhang A, loaikhachhang B "
                    + " where A.MaLKH = B.MaLKH  ";
            String[] arr = {hoten, sodiem, loaikh, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(hoten)) {
                    query = query + " and HoTen like '%" + hoten + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(loaikh)) {
                    query = query + " and A.MaLKH = " + loaikh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + " ";
                }
            }
            query = query + " Order by DiemTL " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchLoaiKH(String hoten, String sodiem, String loaikh, String tinhtrang, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKH), HoTen, DiaChi, SoDT, NgaySN, A.MaLKH, DiemTL, A.TinhTrang "
                    + " from khachhang A, loaikhachhang B "
                    + " where A.MaLKH = B.MaLKH  ";
            String[] arr = {hoten, sodiem, loaikh, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(hoten)) {
                    query = query + " and HoTen like '%" + hoten + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(loaikh)) {
                    query = query + " and A.MaLKH = " + loaikh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + " ";
                }
            }
            query = query + " Order by MaLKH " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTKH(String hoten, String sodiem, String loaikh, String tinhtrang, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKH), HoTen, DiaChi, SoDT, NgaySN, A.MaLKH, DiemTL, A.TinhTrang "
                    + " from khachhang A, loaikhachhang B "
                    + " where A.MaLKH = B.MaLKH  ";
            String[] arr = {hoten, sodiem, loaikh, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(hoten)) {
                    query = query + " and HoTen like '%" + hoten + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(sodiem)) {
                    query = query + " and SoDiem = " + sodiem + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(loaikh)) {
                    query = query + " and A.MaLKH = " + loaikh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + " ";
                }
            }
            query = query + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean UpdateStatusKH(String tinhtrang, int langid, String makh) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khachhang set TinhTrang = " + tinhtrang + " where MaKH = " + makh;
            int n = stmt.executeUpdate(query);
            stmt.close();

            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateLKH(String malkh, int langid, String makh) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khachhang set MaLKH = " + malkh + " where MaKH = " + makh;
            int n = stmt.executeUpdate(query);
            stmt.close();

            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Detail(String makh) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khachhang where MaKH = " + makh + " ";

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhachHang kh = new Beans.KhachHang();
                kh.setMaKH(rss.getInt(1));
                kh.setHoTen(rss.getString(2));
                kh.setDiaChi(rss.getString(3));
                kh.setSoDT(rss.getString(4));
                kh.setNgaySN(rss.getString(5));
                kh.setMaLKH(rss.getInt(6));
                kh.setDiemTL(rss.getInt(7));
                kh.setTinhTrang(rss.getByte(8));
                v.add(kh);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean UpdateDetailkh(String makh, String tenkh, String diachi, String sodt, String birthday, String loaikh) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khachhang set HoTen = '" + tenkh + "', DiaChi = '" + diachi + "', SoDT = '" + sodt + "', NgaySN = '" + birthday + "', MaLKH = " + loaikh + " where  MaKH =" + makh + "";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LaySoTien() {
        int maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select SoTien from doidiem ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static int LaySoDiem() {
        int maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select SoDiem from doidiem ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean UpdateDoiDiem(String sodiem, String sotien) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update doidiem set sodiem = '" + sodiem + "', sotien = '" + sotien + "'";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckKHExist(int makh) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from khachhang where  MaKH = " + makh + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
